﻿using System;
using System.Collections.Generic;
using System.Data.Odbc;
using System.Linq;
using System.Web;

namespace HitachiService.DBAccess
{
    public class ProjectResourceAccess : DataAccess
    {
        public static WholeManageData GetWholeManageDataByResourceId(int resource_id)
        {
            WholeManageData result = new WholeManageData();
            List<ResourceData> resources = ResourceAccess.GetResourcesByParentId(resource_id);
            List<ProjectResourceData> pr = GetProjectResourceByResourceId(resource_id);
            foreach (ResourceData rd in resources)
            {
                List<ProjectResourceData> child_pr = GetProjectResourceByResourceId(rd.ID);
                pr.AddRange(child_pr);
            }
            result.ProjectResource = pr;
            resources.Add(ResourceAccess.GetResourceById(resource_id));

            Dictionary<int, ProjectData> projects = new Dictionary<int,ProjectData>();
            List<ProjectData> projects_list = new List<ProjectData>();
            foreach (ProjectResourceData prd in pr)
            {
                if (!projects.ContainsKey(prd.ProjectID))
                    projects.Add(prd.ProjectID, null);
            }
            foreach (int prj_id in projects.Keys)
            {
                ProjectData pd = ProjectAccess.GetProjectById(prj_id);
                projects_list.AddRange(ProjectAccess.GetOffspringProjects(pd, 1));
            }
            result.Projects = projects_list;
            result.Resources = resources;
            return result;
        }

        public static List<ProjectResourceData> GetProjectResourceByResourceId(int resource_id)
        {
            String sql =
                "select " +
                "gid,project_id,resource_id,relation_flag " +
                "from t_prj_res where resource_id=" + resource_id + ";";
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);

            conn.Open();
            OdbcDataReader reader = cmd.ExecuteReader();
            List<ProjectResourceData> result = new List<ProjectResourceData>();
            while (reader.Read())
            {
                ProjectResourceData prj_res = new ProjectResourceData();
                prj_res.ProjectID = ReadInt(reader["project_id"]);
                prj_res.ResourceID = ReadInt(reader["resource_id"]);
                prj_res.RelationFlag = ReadInt(reader["relation_flag"]);
                prj_res.ID = ReadInt(reader["gid"]);
                result.Add(prj_res);
            }
            conn.Close();
            return result;
        }

        private static int UpdateProjectResource(ProjectResourceData data)
        {
            String sql =
                "update t_prj_res set " +
                "relation_flag=" + data.RelationFlag +
                " where gid=" + data.ID;
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);
            conn.Open();
            int lines = cmd.ExecuteNonQuery();
            conn.Close();
            return lines;
        }

        private static int RemoveProjectResource(ProjectResourceData data)
        {
            String sql =
                "delete from t_prj_res " +
                " where gid=" + data.ID;
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);
            conn.Open();
            int lines = cmd.ExecuteNonQuery();
            conn.Close();
            data.ID = 0;
            return lines;
        }

        private static int InsertProjectResource(ProjectResourceData data)
        {
            String sql =
                "insert into t_prj_res " +
                "(project_id,resource_id,relation_flag) values (" +
                data.ProjectID + "," + data.ResourceID + "," + data.RelationFlag +
                ");";
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);
            conn.Open();
            int lines = cmd.ExecuteNonQuery();
            {
                sql = "select @@IDENTITY;";
                OdbcCommand cmd_last = new OdbcCommand(sql, conn);
                int last_insert_id = Convert.ToInt32(cmd_last.ExecuteScalar());
                data.ID = last_insert_id;
            }
            conn.Close();
            return data.ID;
        }

        public static ProjectResourceData SaveProjectResource(ProjectResourceData data)
        {
            if (data.ID != 0)
            {
                if (data.RelationFlag == 0)
                    RemoveProjectResource(data);
                else
                    UpdateProjectResource(data);
            }
            else
            {
                InsertProjectResource(data);
            }
            return data;
        }
    }
}